package com.sj.excel;

import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.io.FileUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.Test;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;

/**
 * excel转换成DTO
 *
 * @author zhangsj
 * @date: 2021/12/30 23:44
 */
public class ExcelToDtoClient {
    private static final Logger logger = LoggerFactory.getLogger(ExcelToDtoClient.class);

    @Test
    public static void main(String[] args) throws Exception {
        File file = new File(ExcelConst.DIR_PATH); /*绝对路径*/
        ArrayList<File> files = getListFiles(file);
        for (File f : files) {
//            createJAVA(f);
            createJAVAPubServ(f,ExcelConst.CLASS_TYPE_OUT); /*输出文件类型 ？入参、出参DTO*/
        }

    }

    /**
     * 创建java pojo实体类 fmi格式
     *
     * @param file
     * @throws IOException
     * @throws InvalidFormatException
     */
    public static void createJAVAFmi(File file,String classType) throws Exception {

        String fileName = file.getName();
        String name = fileName.substring(0, fileName.lastIndexOf("."));
        String className;
        if ("in".equals(classType)) {
            className = ExcelConst.FILE_NAME_PREFIX_Q + name + ExcelConst.FILE_NAME_SUFFIX__DTO;
        } else {
            className = ExcelConst.FILE_NAME_PREFIX_NULL + name + ExcelConst.FILE_NAME_SUFFIX__DTO;
        }
        File file1;
        FileOutputStream fop;
        List types = new ArrayList();
        List attributes = new ArrayList();
        List notNulls = new ArrayList();
        List marks = new ArrayList();
// List marksEmpty = new ArrayList();
        if (fileName.endsWith("xlsx")) {
            XSSFWorkbook workbooks = new XSSFWorkbook(FileUtils.openInputStream(file));
            XSSFSheet xssfSheet = workbooks.getSheetAt(0);
            int totalRows = xssfSheet.getPhysicalNumberOfRows();
            if (totalRows <= 1) {
                logger.error("excel文件的行号小于等于1");
                throw new Exception("excel文件的行号小于等于1");
            }
            totalRows--;
            System.out.println(className + "total:" + totalRows);
            for (int i = 1; i <= totalRows; i++) {
                XSSFRow row2 = xssfSheet.getRow(i);
                attributes.add(row2.getCell(ExcelConst.ATTRIBUTES_INDEX).getStringCellValue());
                types.add(getObjType(row2.getCell(ExcelConst.TYPES_INDEX).getStringCellValue()));
                notNulls.add(row2.getCell(ExcelConst.NOT_NULL_INDEX).getStringCellValue());
                String marksDtr = "";
                for (int j = ExcelConst.MARKS_INDEX.length - 1; j >= 0; j--) {
                    marksDtr += row2.getCell(ExcelConst.MARKS_INDEX[j]).getStringCellValue();
                }
                marks.add(marksDtr);
            }

        } else if (fileName.endsWith("xls")) {
            HSSFWorkbook workbook = new HSSFWorkbook(FileUtils.openInputStream(file));
//读取默认第一个工作表sheet
            HSSFSheet sheet = workbook.getSheetAt(0);
//获取sheet中最后一行行号
            int lastRowNum = sheet.getLastRowNum();
            for (int i = 1; i <= lastRowNum; i++) {
                HSSFRow row1 = sheet.getRow(i);
                attributes.add(row1.getCell(ExcelConst.ATTRIBUTES_INDEX).getStringCellValue());
                types.add(getObjType(row1.getCell(ExcelConst.TYPES_INDEX).getStringCellValue()));
                notNulls.add(row1.getCell(ExcelConst.NOT_NULL_INDEX).getStringCellValue());
                String marksDtr = "";
                for (int j = ExcelConst.MARKS_INDEX.length - 1, k = 0; j >= 0; j--, k++) {
                    String stringCellValue = row1.getCell(ExcelConst.MARKS_INDEX[k]).getStringCellValue();
                    if (j == ExcelConst.MARKS_INDEX.length - 1) {
                        marksDtr = stringCellValue;
                    } else if (StringUtils.isNotBlank(stringCellValue)) {
                        marksDtr += "-->" + stringCellValue;
                    }
                }
                marks.add(marksDtr);
//marksEmpty.add(row1.getCell(marksEmptyIndex).getStringCellValue());
            }
        }

        StringBuffer sb = new StringBuffer();
        if ("out".equals(classType)) {
            List oa = new ArrayList();
            CollectionUtils.addAll(oa, new Object[attributes.size()]);
            Collections.copy(oa, attributes);
            attributes = CreateUtil.attributesFilter(attributes);
            types = CreateUtil.typesFilter(types);
//                sb.append("//引用、包名、注释等等\n");
            sb.append("import lombok.Data;\n");
            sb.append("import java.io.Serializable;\n");
            sb.append("@Data\n");
            sb.append("public class " + className + " implements Serializable {\n\n");
            sb.append("private static final long serialVersionUID = 1L;\n\n");
            sb.append("private List<Result> result;\n\n");
            sb.append("@Data\n");
            sb.append("public static class Result implements Serializable {\n\n");
            sb.append("private static final long serialVersionUID = 1L;\n\n");

            sb.append(CreateUtil.appendPrivate(types, attributes, marks, oa, notNulls, classType));
            //以下是getset方法
            //sb.append(createUtil.getSet(types, attributes, marks));
            sb.append("}\n");
            sb.append("}");
        } else {
            List oa = new ArrayList();
            CollectionUtils.addAll(oa, new Object[attributes.size()]);
            Collections.copy(oa, attributes);
            attributes = CreateUtil.attributesFilter(attributes);
            types = CreateUtil.typesFilter(types);
            sb.append("import lombok.Data;\n");
            sb.append("import java.io.Serializable;\n");
            sb.append("//引用、包名、注释等等\n");
            sb.append("@Data\n");
            sb.append("public class " + className + " implements Serializable {\n\n");
            sb.append("private static final long serialVersionUID = 1L;\n\n");
            sb.append("private Data data;\n\n");
            sb.append("@lombok.Data\n");
            sb.append("public static class Data implements Serializable {\n\n");
            sb.append("private static final long serialVersionUID = 1L;\n\n");
            sb.append(CreateUtil.appendPrivate(types, attributes, marks, oa, notNulls, classType));
            //以下是getset方法
            //sb.append(createUtil.getSet(types, attributes, marks));
            sb.append("}\n");
            sb.append("}");
        }

        file1 = new File(file.getParent() + "//" + className + ".java");
        fop = new FileOutputStream(file1);
        if (!file1.exists()) {
            file1.createNewFile();
        }
        byte[] contentInBytes = sb.toString().getBytes();
        fop.write(contentInBytes);
        fop.flush();
        fop.close();
        System.out.println("Done");
    }

    /**
     * 创建java pojo实体类 公服格式 pubServ
     *
     * @param file
     * @param classType
     * @throws IOException
     * @throws InvalidFormatException
     */
    public static void createJAVAPubServ(File file, String classType) throws Exception {

        String fileName = file.getName();
        String name = fileName.substring(0, fileName.lastIndexOf("."));
        String className;
        if ("in".equals(classType)) {
            className = ExcelConst.FILE_NAME_PREFIX_Q + name + ExcelConst.FILE_NAME_SUFFIX__DTO;
        } else {
            className = ExcelConst.FILE_NAME_PREFIX_NULL + name + ExcelConst.FILE_NAME_SUFFIX__DTO;
        }
        File file1;
        FileOutputStream fop;
        List types = new ArrayList();
        List attributes = new ArrayList();
        List notNulls = new ArrayList();
        List marks = new ArrayList();
// List marksEmpty = new ArrayList();
        if (fileName.endsWith("xlsx")) {
            XSSFWorkbook workbooks = new XSSFWorkbook(FileUtils.openInputStream(file));
            XSSFSheet xssfSheet = workbooks.getSheetAt(0);
            int totalRows = xssfSheet.getPhysicalNumberOfRows();
            if (totalRows <= 1) {
                logger.error("excel文件的行号小于等于1");
                throw new Exception("excel文件的行号小于等于1");
            }
            totalRows--;
            System.out.println(className + "total:" + totalRows);
            for (int i = 1; i <= totalRows; i++) {
                XSSFRow row2 = xssfSheet.getRow(i);
                attributes.add(row2.getCell(ExcelConst.ATTRIBUTES_INDEX).getStringCellValue());
                types.add(getObjType(row2.getCell(ExcelConst.TYPES_INDEX).getStringCellValue()));
                notNulls.add(row2.getCell(ExcelConst.NOT_NULL_INDEX).getStringCellValue());
                String marksDtr = "";
                for (int j = ExcelConst.MARKS_INDEX.length - 1; j >= 0; j--) {
                    marksDtr += row2.getCell(ExcelConst.MARKS_INDEX[j]).getStringCellValue();
                }
                marks.add(marksDtr);
            }

        } else if (fileName.endsWith("xls")) {
            HSSFWorkbook workbook = new HSSFWorkbook(FileUtils.openInputStream(file));
//读取默认第一个工作表sheet
            HSSFSheet sheet = workbook.getSheetAt(0);
//获取sheet中最后一行行号
            int lastRowNum = sheet.getLastRowNum();
            for (int i = 1; i <= lastRowNum; i++) {
                HSSFRow row1 = sheet.getRow(i);
                attributes.add(row1.getCell(ExcelConst.ATTRIBUTES_INDEX).getStringCellValue());
                types.add(getObjType(row1.getCell(ExcelConst.TYPES_INDEX).getStringCellValue()));
                notNulls.add(row1.getCell(ExcelConst.NOT_NULL_INDEX).getStringCellValue());
                String marksDtr = "";
                for (int j = ExcelConst.MARKS_INDEX.length - 1, k = 0; j >= 0; j--, k++) {
                    String stringCellValue = row1.getCell(ExcelConst.MARKS_INDEX[k]).getStringCellValue();
                    if (j == ExcelConst.MARKS_INDEX.length - 1) {
                        marksDtr = stringCellValue;
                    } else if (StringUtils.isNotBlank(stringCellValue)) {
                        marksDtr += "-->" + stringCellValue;
                    }
                }
                marks.add(marksDtr);
//marksEmpty.add(row1.getCell(marksEmptyIndex).getStringCellValue());
            }
        }

        StringBuffer sb = new StringBuffer();
        if ("out".equals(classType)) {
            List oa = new ArrayList();
            CollectionUtils.addAll(oa, new Object[attributes.size()]);
            Collections.copy(oa, attributes);
            attributes = CreateUtil.attributesFilter(attributes);
            types = CreateUtil.typesFilter(types);
//                sb.append("//引用、包名、注释等等\n");
            sb.append("import lombok.Data;\n");
            sb.append("import java.io.Serializable;\n");
            sb.append("@Data\n");
            sb.append("public class " + className + " implements Serializable {\n\n");
            sb.append(CreateUtil.appendPrivate(types, attributes, marks, oa, notNulls, classType));
            //以下是getset方法
            //sb.append(createUtil.getSet(types, attributes, marks));
            sb.append("}");
        } else {
            List oa = new ArrayList();
            CollectionUtils.addAll(oa, new Object[attributes.size()]);
            Collections.copy(oa, attributes);
            attributes = CreateUtil.attributesFilter(attributes);
            types = CreateUtil.typesFilter(types);
            // TODO: 2021/12/31 zhangsj 引用、包名、注释等等
            sb.append("import lombok.Data;\n");
            sb.append("import java.io.Serializable;\n");
            sb.append("@Data\n");
            sb.append("public class " + className + " implements Serializable {\n\n");
            sb.append(CreateUtil.appendPrivate(types, attributes, marks, oa, notNulls, classType));
            //以下是getset方法
            //sb.append(createUtil.getSet(types, attributes, marks));
            sb.append("}");
        }

        file1 = new File(file.getParent() + "//" + className + ".java");
        fop = new FileOutputStream(file1);
        if (!file1.exists()) {
            file1.createNewFile();
        }
        byte[] contentInBytes = sb.toString().getBytes();
        fop.write(contentInBytes);
        fop.flush();
        fop.close();
        System.out.println("Done");
    }

    /**
     * 根据名称获取到java属性类型
     *
     * @param stringCellValue
     * @return
     */
    private static Object getObjType(String stringCellValue) {
        if ("字符型".equals(stringCellValue)) {
            return "String";
        }
        if ("日期型".equals(stringCellValue)) {
            return "LocalDate";
        }
        if ("日期时间型".equals(stringCellValue)) {
            return "LocalDateTime";
        }
        if ("数值型".equals(stringCellValue)) {
            return "BigDecimal";
        }
        return stringCellValue;
    }

    /**
     * 获取到所有的文件
     *
     * @param obj
     * @return
     */
    public static ArrayList getListFiles(Object obj) {
        File directory = null;
        if (obj instanceof File) {
            directory = (File) obj;
        } else {
            directory = new File(obj.toString());
        }
        ArrayList files = new ArrayList();
        if (directory.isFile()) {
            files.add(directory);
            return files;
        } else if (directory.isDirectory()) {
            File[] fileArr = directory.listFiles();
            for (int i = 0; i < fileArr.length; i++) {
                File fileOne = fileArr[i];
                files.addAll(getListFiles(fileOne));
            }

        }
        return files;
    }
}
